/*
	exec sp_backlog_asofdate_report_proc '1040,964,963','0','176,140,210','0','03/01/2007','0','REVDATE',1,'bymonth','xlreport'
	exec sp_backlog_asofdate_report_proc '0','0','0','0','01/05/2008','0','REVDATE',1,'bymonth','XLREPORT'
*/
ALTER PROC sp_backlog_asofdate_report_proc

@PRODUCT_ID 		VARCHAR(200),
@SO_NUMBER 		VARCHAR(15),
@CUSTOMER_ID 		VARCHAR(200),
@SALES_PERSON 		VARCHAR(500),
@ASOFDATE 		VARCHAR(15),
@BACKLOG_TYPE 		VARCHAR(10),
@CATEGORY		VARCHAR(100),
@ORDER_TYPE		INT,
@BY_PERIOD 		VARCHAR(20),
@REPORT_TYPE		VARCHAR(20)
 
AS
BEGIN
CREATE TABLE #BACKLOG_HISTORY_TABLE (	
				[CUSTOMER NAME] VARCHAR(65),[CUSTOMER NUMBER] VARCHAR(50),[END CUSTOMER] VARCHAR(65),
				[END USER] VARCHAR(65),[SALESORDER NUMBER] VARCHAR(50),[LINEITEM NO] INT,
				[REVLEVEL] INT ,[CUSTOMER PO] VARCHAR(50),
				[SO BOOKING DATE] DATETIME,[SO BOOKING DATE WORK WEEK NO] INT,[SO BOOKING DATE QUARTER] INT, 
				[SALES PERSON] VARCHAR(50), [REGION REP] VARCHAR(50),[SALES HOLD] VARCHAR(10), 
				[MARKETING HOLD] VARCHAR(10),[MANUFACTURING HOLD] VARCHAR(10),[FINANCE HOLD] VARCHAR(10), 
				[PAY TERMS] VARCHAR(50),[DELIVERY TERMS] VARCHAR(50),[SHIPPING METHOD] VARCHAR(50),
				[REQUESTED DATE] DATETIME,[REQUESTED DATE WORK WEEK NO] INT,[REQUESTED DATE QUARTER] INT,
				[SCHEDULED SHIP DATE] DATETIME,[SCHEDULE DATE WORK WEEK NO] INT,[SCHEDULE DATE QUARTER] INT, 
				[REVISED SHIP DATE] DATETIME,[REVISED DATE WORK WEEK NO] INT,[REVISED DATE QUARTER] INT,  
				[PART_NUMBER] VARCHAR(50), [REQUESTED QYT] INT,[REMANING QTY] INT,
				[UNIT COST] FLOAT,EXPECTED_COST FLOAT,STATUS VARCHAR(20),[PERIODICALLY VALUE] int,
				ACTUAL_YEAR INT,cool_is_cancel INT,IS_EXPEDITE INT,ISCONSIGNMENT BIT,[DATE ORDER] DATETIME, [CANCELLATION] VARCHAR(30) DEFAULT '' )

	DECLARE @START_DATE 	DATETIME
	DECLARE @END_DATE 	DATETIME
	DECLARE @TEMP_VARCHAR 	VARCHAR(20)
	
	DECLARE @PARAMDEFINITION 	NVARCHAR(500)
	DECLARE @ISCONSIGNMENT BIT

	DECLARE @SQLSTRING 	NVARCHAR(4000)
	DECLARE @SQLSTRING1 	NVARCHAR(4000)

	SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
	SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
	SET @TEMP_VARCHAR = NULL
	SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
	SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
	
	IF @BACKLOG_TYPE = 'ALL'
	BEGIN
		SET @BACKLOG_TYPE = 0
	END

	--SET @PARAMDEFINITION = N'@PRM_BY_PERIOD VARCHAR(50),@PRM_CATEGORY VARCHAR(50),@PRM_ORDER_TYPE INT, @PRM_PRODUCT_ID VARCHAR(50),@PRM_SO_NUMBER VARCHAR(50),@PRM_CUSTOMER_ID VARCHAR(50),@PRM_SALES_PERSON VARCHAR(50),@PRM_BACKLOG_TYPE VARCHAR(20),@PRM_START_DATE DATETIME,@PRM_END_DATE DATETIME'

	SET @SQLSTRING = N' INSERT INTO #BACKLOG_HISTORY_TABLE 
	 select 
		Customer_Name, Customer_Number,
		(CASE 	WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.CUSTOMER_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
 		END),(CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
				(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN 
					( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.CUSTOMER_ID ) 
				 	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID ) END)
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END),
		BHT.SalesOrder_No,LineItem_No,
		( select max(ChangeNo) from change_sales_order where salesorder_id = BHT.SalesOrder_ID and CSO_Is_Sample = '''+convert(varchar, @ORDER_TYPE)+'''), 
		Customer_PO_Number,Booking_Date ,Booking_Date_WW_No, Booking_Date_Qtr_No,
		SALES_PERSON,BHT.REGIONAL_REP,Sales_Hold,Marketing_Hold, Operations_Hold,Finance_Hold, 
		SO.SHIPPING_TERMS,SO.DELIVERY_TERMS,SO_SHIPPING_METHOD,	
		Req_Dock_Date, Req_Dock_Date_WW_No, Req_Dock_Date_Qtr_No,
		Schedule_Date, Schedule_Date_WW_No, Schedule_Date_Qtr_No,
		ISNULL(Revised_Date,Schedule_Date),
		ISNULL(Revised_Date_WW_No,Schedule_Date_WW_No),
		ISNULL(Revised_Date_Qtr_No,Schedule_Date_Qtr_No),
		--Revised_Date,Revised_Date_WW_No , Revised_Date_Qtr_No,
		Part_Number,BK_Requested_Qty,( BK_Requested_Qty - isNUll(BK_Shipped_Qty, 0) ),BK_Unit_Cost,
		(( BK_Requested_Qty - isNUll(BK_Shipped_Qty, 0) ) * BK_Unit_Cost ),BK_Backlog_Type,
		( CASE 
			WHEN '''+ @CATEGORY +''' = ''reqDockDate'' THEN
			 ( CASE WHEN '''+ @BY_PERIOD +''' = ''byMonth'' THEN DATEPART(m, Req_Dock_Date) 
				WHEN '''+ @BY_PERIOD +''' = ''byQuarter'' THEN Req_Dock_Date_Qtr_No
				/*WHEN '''+ @BY_PERIOD +''' = ''byWeek'' THEN Req_Dock_Date_WW_No*/
				ELSE DATEPART(yyyy, Req_Dock_Date)
			  END
			 )
			WHEN '''+ @CATEGORY +''' = ''scheduleDate'' THEN
			 ( CASE WHEN '''+ @BY_PERIOD +''' = ''byMonth'' THEN DATEPART(m, Schedule_Date) 
				WHEN '''+ @BY_PERIOD +''' = ''byQuarter'' THEN Schedule_Date_Qtr_No
				/*WHEN '''+ @BY_PERIOD +''' = ''byWeek'' THEN Schedule_Date_WW_No*/
				ELSE DATEPART(yyyy, Schedule_Date)
			  END
			 )
			WHEN '''+ @CATEGORY +''' = ''revDate'' THEN
			 ( CASE  WHEN '''+ @BY_PERIOD +''' = ''byMonth'' THEN DATEPART(m, ISNULL(Revised_Date, Schedule_Date)) 
				WHEN '''+ @BY_PERIOD +''' = ''byQuarter'' THEN ISNULL(Revised_Date_Qtr_No, Schedule_Date_Qtr_No)
				/*WHEN '''+ @BY_PERIOD +''' = ''byWeek'' THEN ISNULL(Revised_Date_WW_No, Schedule_Date_WW_No)*/
				ELSE DATEPART(yyyy, ISNULL(Revised_Date, Schedule_Date))
			  END
			 )
			WHEN '''+ @CATEGORY +''' = ''salesOrder'' OR '''+ @CATEGORY +''' = ''partNumber'' THEN DATEPART(yyyy, Booking_Date)
			  
		  END
		) AS PERIODICALLY_VALUE, '
	SET @SQLSTRING1 = ' 
		(CASE
			WHEN '''+ @CATEGORY +''' = ''reqDockDate'' THEN  DATEPART(yyyy, Req_Dock_Date)
			WHEN '''+ @CATEGORY +''' = ''scheduleDate'' THEN DATEPART(yyyy, Schedule_Date)
			WHEN '''+ @CATEGORY +''' = ''revDate'' THEN DATEPART(yyyy, ISNULL(Revised_Date, Schedule_Date))
			WHEN '''+ @CATEGORY +''' = ''salesOrder'' OR '''+ @CATEGORY +''' = ''partNumber'' THEN DATEPART(yyyy, Booking_Date)
		END),BK_IS_CANCEL,BK_IS_EXPEDITE,SO_IS_CONSIGNMENT,
		(CASE	WHEN '''+ @CATEGORY +''' = ''reqDockDate'' THEN Req_Dock_Date
			WHEN '''+ @CATEGORY +''' = ''scheduleDate'' THEN Schedule_Date
			WHEN '''+ @CATEGORY +''' = ''revDate'' THEN ISNULL(Revised_Date, Schedule_Date)
			ELSE Booking_Date
		END) 	
		,(CASE WHEN BK_IS_CANCEL = 0  THEN ''Cancelled but not Approved'' ELSE '''' END)
	from 
		Backlog_History_Table BHT, Sales_Order SO 
	where 
		BHT.SalesOrder_ID = SO.SalesOrder_ID 
		AND BHT.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''
		
	--SET @SQLSTRING = @SQLSTRING + ' AND BHT.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''  
	
	IF ( @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND SO.CUSTOMER_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMER_ID +'''))'
	END	
	IF ( @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND PRODUCT_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
	END
	IF ( @SO_NUMBER IS NOT NULL AND @SO_NUMBER <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND SALESORDER_NO = '''+ @SO_NUMBER+''''
	END
	IF ( @SALES_PERSON IS NOT NULL AND @SALES_PERSON <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALES_PERSON +'''))'
	END
	IF ( @BACKLOG_TYPE IS NOT NULL AND @BACKLOG_TYPE <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND BK_BACKLOG_TYPE = '''+ @BACKLOG_TYPE+''''
	END

	IF ( @ORDER_TYPE IS NOT NULL AND @ORDER_TYPE <> 2 )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND SO_IS_CONSIGNMENT = 1 AND SO.SO_IS_SAMPLE = '''+ CONVERT(VARCHAR,@ORDER_TYPE) +''''
	END
	ELSE IF ( @ORDER_TYPE IS NOT NULL AND @ORDER_TYPE = 2 )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND SO_IS_CONSIGNMENT = 0 '
	END

	--PRINT '@SQLSTRING :- '+@SQLSTRING
	--PRINT '@SQLSTRING1 :- '+@SQLSTRING1
	exec (@SQLSTRING+@SQLSTRING1)
	
	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
		IF @CATEGORY = 'partNumber'
		BEGIN
			select 
				[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],
				[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
				[SO BOOKING DATE],[SO BOOKING DATE WORK WEEK NO],[SO BOOKING DATE QUARTER],
				[SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
				[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],
				[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],[REQUESTED DATE QUARTER], 
				[SCHEDULED SHIP DATE],[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER], 
				[REVISED SHIP DATE],[REVISED DATE WORK WEEK NO],[REVISED DATE QUARTER], 
				[PART_NUMBER] , [REQUESTED QYT] ,[UNIT COST] ,[REMANING QTY],
				( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE],
				[CANCELLATION]
				 --,[PERIODICALLY VALUE]
		
			FROM #BACKLOG_HISTORY_TABLE
			order by [PART_NUMBER],[CUSTOMER NAME]
		END
		ELSE IF @CATEGORY = 'salesOrder'
		BEGIN
			select 
				[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],
				[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
				[SO BOOKING DATE],[SO BOOKING DATE WORK WEEK NO],[SO BOOKING DATE QUARTER], 
				[SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
				[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],
				[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],[REQUESTED DATE QUARTER], 
				[SCHEDULED SHIP DATE],[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER], 
				[REVISED SHIP DATE],[REVISED DATE WORK WEEK NO],[REVISED DATE QUARTER], 
				[PART_NUMBER], [REQUESTED QYT],[UNIT COST] ,[REMANING QTY],
				( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE],
				[CANCELLATION]
				--,[PERIODICALLY VALUE]
		
			FROM #BACKLOG_HISTORY_TABLE
			order by [SALESORDER NUMBER],[CUSTOMER NAME]
		END
		ELSE
		BEGIN
			select 
				[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],
				[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
				[SO BOOKING DATE],[SO BOOKING DATE WORK WEEK NO],[SO BOOKING DATE QUARTER], 
				[SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
				[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],
				[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],[REQUESTED DATE QUARTER], 
				[SCHEDULED SHIP DATE],[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER], 
				[REVISED SHIP DATE],[REVISED DATE WORK WEEK NO],[REVISED DATE QUARTER], 
				[PART_NUMBER], [REQUESTED QYT],[UNIT COST] ,[REMANING QTY],
				( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE],
				[CANCELLATION]
				 --,[PERIODICALLY VALUE]
		
			FROM #BACKLOG_HISTORY_TABLE
			order by [PERIODICALLY VALUE],[CUSTOMER NAME]
		END
	END
	ELSE
	BEGIN
		IF @CATEGORY = 'partNumber'
		BEGIN
			select 
				[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
				[SO BOOKING DATE], [SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
				[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],IS_EXPEDITE,
				[REQUESTED DATE], [SCHEDULED SHIP DATE] , [REVISED SHIP DATE] , [PART_NUMBER] , [REQUESTED QYT] , 
				[UNIT COST] ,[REMANING QTY],( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE],isnull([PERIODICALLY VALUE],0) [PERIODICALLY VALUE],ACTUAL_YEAR,
				(CASE  	
					WHEN STATUS = 'open'  THEN 'Open Orders'
					ELSE 
						( CASE 
							WHEN ( [SALES HOLD] = 'X' OR [MARKETING HOLD] = 'X' OR [MANUFACTURING HOLD] = 'X' OR [FINANCE HOLD] = 'X' ) THEN 'General Holds'
					   		ELSE 'Designated Holds' 
		 				END) 
				END) AS HOLD_TYPE,cool_is_cancel,ISCONSIGNMENT
			
			FROM #BACKLOG_HISTORY_TABLE
			order by [PART_NUMBER],[CUSTOMER NAME]
		END
		ELSE IF @CATEGORY = 'salesOrder'
		BEGIN
			select 
				[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
				[SO BOOKING DATE], [SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
				[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],IS_EXPEDITE,
				[REQUESTED DATE], [SCHEDULED SHIP DATE] , [REVISED SHIP DATE] , [PART_NUMBER] , [REQUESTED QYT] , 
				[UNIT COST] ,[REMANING QTY],( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE],isnull([PERIODICALLY VALUE],0) [PERIODICALLY VALUE],ACTUAL_YEAR,
				(CASE  	
					WHEN STATUS = 'open'  THEN 'Open Orders'
					ELSE 
						( CASE 
							WHEN ( [SALES HOLD] = 'X' OR [MARKETING HOLD] = 'X' OR [MANUFACTURING HOLD] = 'X' OR [FINANCE HOLD] = 'X' ) THEN 'General Holds'
					   		ELSE 'Designated Holds' 
		 				END) 
				END) AS HOLD_TYPE,cool_is_cancel,ISCONSIGNMENT
			FROM #BACKLOG_HISTORY_TABLE
			order by [SALESORDER NUMBER],[CUSTOMER NAME]
		END
		ELSE
		BEGIN
			select 
				[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
				[SO BOOKING DATE], [SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
				[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],IS_EXPEDITE,
				[REQUESTED DATE], [SCHEDULED SHIP DATE] , [REVISED SHIP DATE] , [PART_NUMBER] , [REQUESTED QYT] , 
				[UNIT COST] ,[REMANING QTY],( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE],isnull([PERIODICALLY VALUE],0) [PERIODICALLY VALUE],ACTUAL_YEAR,
				(CASE  	
					WHEN STATUS = 'open'  THEN 'Open Orders'
					ELSE 
						( CASE 
							WHEN ( [SALES HOLD] = 'X' OR [MARKETING HOLD] = 'X' OR [MANUFACTURING HOLD] = 'X' OR [FINANCE HOLD] = 'X' ) THEN 'General Holds'
					   		ELSE 'Designated Holds' 
		 				END) 
				END) as HOLD_TYPE, cool_is_cancel,ISCONSIGNMENT,STATUS
			FROM #BACKLOG_HISTORY_TABLE
			order by [DATE ORDER] 
		END
	END
END 
